package org.realogic.cfm.persistence.dialect.db;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.session.Configuration;
import org.realogic.cfm.persistence.KParameter;
import org.realogic.cfm.persistence.dialect.AbstractDialect;
import org.realogic.cfm.persistence.query.QueryCondition;

/**
 * Sql 2005的方言实现
 */
public class SQLServer2005Dialect extends AbstractDialect {

	private static final Pattern ALIAS_PATTERN = Pattern.compile("\\sas\\s[^,]+(,?)");

	public boolean supportsLimit() {
		return true;
	}

	public String getSelectCountSQL4GroupBy(String sql) {
		return "select count(1) from (" + sql + ") _tmp_size_";
	}

	/**
	 * Add a LIMIT clause to the given SQL SELECT
	 * <p/>
	 * The LIMIT SQL will look like:
	 * <p/>
	 * WITH query AS (SELECT TOP 100 percent ROW_NUMBER() OVER (ORDER BY
	 * CURRENT_TIMESTAMP) as __row_number__, * from table_name) SELECT * FROM
	 * query WHERE __row_number__ BETWEEN :offset and :lastRows ORDER BY
	 * __row_number__
	 * 
	 * @param querySqlString
	 *            The SQL statement to base the limit query off of.
	 * @param offset
	 *            Offset of the first row to be returned by the query
	 *            (zero-based)
	 * @param limit
	 *            Maximum number of rows to be returned by the query
	 * @return A new SQL statement with the LIMIT clause applied.
	 */

	public String getLimitString(String sql, int offset, int limit) {
		StringBuilder sb = new StringBuilder(sql.trim().toLowerCase());
		int orderByIndex = sb.indexOf("order by");
		CharSequence orderby = ((CharSequence) (orderByIndex <= 0 ? "ORDER BY CURRENT_TIMESTAMP" : sb.subSequence(orderByIndex, sb.length())));
		if (orderByIndex > 0) {
			sb.delete(orderByIndex, orderByIndex + orderby.length());
		}
		replaceDistinctWithGroupBy(sb);
		insertRowNumberFunction(sb, orderby);
		sb.insert(0, "WITH query AS (").append(") SELECT * FROM query ");
		sb.append("WHERE __row_nr__ > ? AND __row_nr__ <= ?");
		return sb.toString();
	}

	protected void insertRowNumberFunction(StringBuilder sqlBuilder, CharSequence orderby) {
		int selectEndIndex = sqlBuilder.indexOf("from");
		sqlBuilder.insert(selectEndIndex - 1, (new StringBuilder()).append(", ROW_NUMBER() OVER (").append(orderby).append(") as __row_nr__").toString());
	}

	protected static void replaceDistinctWithGroupBy(StringBuilder sqlBuilder) {
		int distinctIndex = sqlBuilder.indexOf("distinct");
		int selectEndIndex = sqlBuilder.indexOf("from");
		if (distinctIndex > 0 && distinctIndex < selectEndIndex) {
			sqlBuilder.delete(distinctIndex, distinctIndex + "distinct".length() + 1);
			sqlBuilder.append(" group by").append(getSelectFieldsWithoutAliases(sqlBuilder));
		}
	}

	protected static CharSequence getSelectFieldsWithoutAliases(StringBuilder sqlBuilder) {
		String select = sqlBuilder.substring(sqlBuilder.indexOf("select") + "select".length(), sqlBuilder.indexOf("from"));
		return stripAliases(select);
	}

	protected static String stripAliases(String str) {
		Matcher matcher = ALIAS_PATTERN.matcher(str);
		return matcher.replaceAll("$1");
	}

	static String getOrderByPart(String sql) {
		String loweredString = sql.toLowerCase();
		int orderByIndex = loweredString.indexOf("order by");
		if (orderByIndex != -1) {
			// if we find a new "order by" then we need to ignore
			// the previous one since it was probably used for a subquery
			return sql.substring(orderByIndex);
		} else {
			return "";
		}
	}

	public List<ParameterMapping> newParameterMappings(MappedStatement mappedStatement, BoundSql boundSql, QueryCondition queryCondition, boolean isPaging) {
		List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
		Configuration configuration = mappedStatement.getConfiguration();
		ParameterMapping.Builder builder = null;
		List<KParameter> tmpParamList = queryCondition.getParameterList();
		int size = tmpParamList == null ? 0 : tmpParamList.size();
		KParameter tmpParam = null;
		for (int i = 0; i < size; i++) {
			tmpParam = tmpParamList.get(i);
			builder = new ParameterMapping.Builder(configuration, tmpParam.getName(), tmpParam.geClassClass());
			newParameterMappings.add(builder.build());
			boundSql.setAdditionalParameter(tmpParam.getName(), tmpParam.getValueValue());
		}
		// 分页
		if (isPaging && queryCondition.isDisplayByPage()) {
			int pageSize = queryCondition.getPageSize();
			int index = (queryCondition.getPageNo() - 1) * pageSize;
			int start = index < 0 ? 0 : index;
			int end = index + pageSize;
			builder = new ParameterMapping.Builder(configuration, "_start_", Integer.class);
			newParameterMappings.add(builder.build());
			boundSql.setAdditionalParameter("_start_", start);
			builder = new ParameterMapping.Builder(configuration, "_end_", Integer.class);
			newParameterMappings.add(builder.build());
			boundSql.setAdditionalParameter("_end_", end);
		}
		return newParameterMappings;
	}
}
